/*
	exec sp_booking_test_report 'booking',1
*/
ALTER                        proc sp_booking_test_report
@date_category varchar(50),
@order_type int 

as

declare @cursor_So_Id_I int, @cursor_Li_Id_I int, @cust_Id_I int
declare @so_Id_I int, @cool_Line_Item_Id_I int, @cool_Line_Item_No_I int, @bkd_Qty_I int, @prod_Id_I int
declare @cco_Ch_No_I int, @prev_Ch_No_I int, @curr_Ch_No_I int, @temp_Ch_No_I int, @so_Bk_Yr_I int, @so_Bk_Mt_I int, @so_Bk_Qt_I int, @so_Bk_Qt_End_I int
declare @unit_Cost_D float, @amt_D float
declare @so_Bk_Dt_DT datetime, @so_Rev_Dt_DT datetime, @so_Schd_Dt_DT datetime, @so_Req_Dk_Dt_DT datetime
declare @so_No_V varchar(50), @so_Sales_Person_V varchar (50), @so_End_User_V varchar(65), @so_regional_Rep_V varchar(50)
declare @cust_Cust_Name_V varchar(65), @prod_Name_V varchar(50), @cust_Region_V varchar(50)
declare @temp_Unit_Cost_D float, @temp_Amt_D float
declare @temp_Qty_I int
declare @insert_Unit_Cost_D float, @insert_Amt_D float
declare @insert_Qty_I int, @neg_Pos_Bk_I int

declare @ch_date datetime 
declare @act_year int

declare @max_ch_no int 
DECLARE @SO_ODM_ID INT
declare @EndCustomer varchar(65)

declare @results Table (SalesOrderNo varchar(50), SalesOrderID int, CustomerID int, LineItem_ID int, LineItem_No int, 
			Product_ID int, Quantity int,UnitCost float,Amount float, BookDate datetime, ChangeDate datetime, RequestedDockDate datetime, ScheduleDate datetime, RevisedDate datetime, Status varchar(20), NegativePositiveBk int, SalesPerson varchar(50), EndUser varchar(65), RepOffice varchar(50), CustomerName Varchar(65), ProductName varchar (50), Region varchar(50), BookingYear int, BookingMonth int, BookingQuarter int, QuarterEnd int,ActYear int,EndCustomer varchar(65))
declare @tempTable Table (SalesOrder_ID int, LineItem_ID int, ChangeNo int, Qty int, UnitCost float, Amount float)

--salesorder cursor
	declare mainCursor cursor for 
		select distinct ccol.SalesOrder_ID from customer_change_order_lineItem ccol, Sales_order so where so.SalesOrder_id = ccol.SalesOrder_ID and so.SO_Is_Sample = @order_type  order by ccol.SalesOrder_ID 
		open mainCursor
		fetch next from mainCursor into @cursor_So_Id_I
		while @@fetch_status = 0
		begin
			set @so_Bk_Dt_DT = null 
			select @so_Bk_Dt_DT = SO_Booking_Date from Sales_Order where SalesOrder_ID =  @cursor_So_Id_I and Sales_Order.SO_Is_Sample = @order_type			

			--lineitem cursor
			declare ItemLevelCursor cursor for
			select distinct LineItem_ID from Customer_Change_Order_LineItem ccol where salesOrder_ID = @cursor_So_Id_I			
			open ItemLevelCursor
			fetch next from ItemLevelCursor into @cursor_Li_Id_I 
			while @@fetch_status = 0
			begin
			-- save the line item info cursor
				declare LineItemCursor cursor for
				select Salesorder_ID, LineItem_ID, ChangeNo, CCO_Line_Item_No, Product_ID,CCO_Qty, CCO_Unit_Cost,  CCO_Amount, CCO_Requested_Date, CCO_Schedule_Date, CCO_Revised_Date,CCO.TimeStamp  
				from customer_change_order_lineitem CCO where SalesOrder_ID = @cursor_So_Id_I and LineItem_ID = @cursor_Li_Id_I ORDER BY ChangeNo				
				
				open LineItemCursor
				fetch next from LineItemCursor into @so_Id_I, @cool_Line_Item_Id_I, @cco_Ch_No_I, @cool_Line_Item_No_I, @prod_Id_I, @bkd_Qty_I,@unit_Cost_D,
						@amt_D, @so_Req_Dk_Dt_DT, @so_Schd_Dt_DT, @so_Rev_Dt_DT,@ch_date
				while @@fetch_status = 0
				begin
					set @neg_Pos_Bk_I=0
					select @so_No_V= SO.SalesOrder_NO, @cust_Id_I = SO.Customer_ID, @so_sales_Person_V= SO.SO_Sales_Person, 
						@so_End_User_V = CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID <= 0  ) 
								THEN ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.Customer_ID )
								ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ODM_ID )
								END,
						@so_regional_Rep_V = SO.Regional_Rep,
						@EndCustomer = CASE WHEN ( SO.SO_ENDCUSTOMER_ID IS NULL OR SO.SO_ENDCUSTOMER_ID <= 0  ) 
								THEN (CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID <= 0 ) 
									THEN ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.Customer_ID )
									ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID =  SO.SO_ODM_ID )
									END) 
								ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ENDCUSTOMER_ID)
								END 
						from Sales_Order SO where SO.salesorder_ID = @so_ID_I and SO.SO_Is_Sample = @order_type
					select @cust_Cust_Name_V=C_Name, @cust_Region_V=C_Region from Customer where Customer_ID = @cust_Id_I
					select @prod_Name_V = part_Number from product where Product_ID = @prod_Id_I
					if @cco_Ch_No_I = 0
					begin
						select @ch_date = CCO_Booking_Date from Customer_Change_Order_lineitem CCO where lineitem_id = @cursor_Li_Id_I and ChangeNo = @cco_Ch_No_I
						if (@ch_date is null)
						begin
							set @ch_date = @so_Bk_Dt_DT
						end
						if ( @date_category is not null AND @date_category = 'BOOKING' )
						begin
							set @so_Bk_Yr_I = DATEPART(yyyy, @so_Bk_Dt_DT)
							set @so_BK_Mt_I = DATEPART(m, @so_Bk_Dt_DT)
						end
						else
						begin
							set @so_Bk_Yr_I = DATEPART(yyyy, @ch_date)
							set @so_BK_Mt_I = DATEPART(m, @ch_date)
						end

						set @act_year = @so_Bk_Yr_I
						if (@so_BK_Mt_I >=1 and @so_BK_Mt_I <=3)
						begin
							set @so_Bk_Qt_I = 1
							set @so_Bk_Qt_End_I =3
						end
						else if (@so_BK_Mt_I >=4 and @so_BK_Mt_I <=6)
						begin
							set @so_Bk_Qt_I = 2
							set @so_Bk_Qt_End_I =6
						end
						else if (@so_BK_Mt_I >=7 and @so_BK_Mt_I <=9)
						begin
							set @so_Bk_Qt_I = 3
							set @so_Bk_Qt_End_I = 9
						end						
						else if (@so_BK_Mt_I >=10 and @so_BK_Mt_I <=12)
						begin
							set @so_Bk_Qt_I = 4
							set @so_Bk_Qt_End_I =12
						end
						insert into @results values(@so_No_V, @so_Id_I, @cust_Id_I, @cool_Line_Item_Id_I, @cool_Line_Item_No_I, 
									@prod_Id_I, @bkd_Qty_I,@unit_Cost_D,@amt_D, @so_Bk_Dt_DT,@ch_date,@so_Req_Dk_Dt_DT,@so_Schd_Dt_DT, @so_Rev_Dt_DT,null,@neg_Pos_Bk_I,@so_Sales_Person_V,@so_End_User_V,@so_regional_Rep_V,@cust_Cust_Name_V,@prod_Name_V, @cust_Region_V,@so_Bk_Yr_I, @so_Bk_Mt_I, @so_Bk_Qt_I, @so_Bk_Qt_End_I,@act_year,@EndCustomer)


						insert into @tempTable values (@so_Id_I, @cool_Line_Item_Id_I, @cco_Ch_No_I, @bkd_Qty_I,@unit_Cost_D,@amt_D)
					end
			
					else
					begin
						select @temp_Ch_No_I= max(ChangeNo) from @tempTable where 
							SalesOrder_ID = @so_Id_I and LineItem_ID = @cool_Line_Item_Id_I
				
						select @temp_Unit_Cost_D=UnitCost, @temp_Amt_D=Amount, @temp_Qty_I=Qty from @tempTable
							where salesorder_id = @so_Id_I and LineItem_ID = @cool_Line_Item_Id_I and ChangeNo = @temp_Ch_No_I
				
						--look for the changes in amt or qty or price
						set @insert_Unit_Cost_D = @unit_Cost_D - @temp_Unit_Cost_D

						set @insert_Qty_I = @bkd_Qty_I - @temp_Qty_I
															
						--If there are no changes then set them back to default
						if @insert_Unit_Cost_D = 0
							set @insert_Unit_Cost_D = @unit_Cost_D
						if @insert_Qty_I = 0
							set @insert_Qty_I = @bkd_Qty_I

						set @insert_Amt_D = @insert_Unit_Cost_D * @insert_Qty_I

						insert into @tempTable values (@so_Id_I, @cool_Line_Item_Id_I, @cco_Ch_No_I, @bkd_Qty_I,@unit_Cost_D,@amt_D)
						
						if ( @date_category is not null AND @date_category = 'BOOKING' )
						begin
							set @so_Bk_Yr_I = DATEPART(yyyy, @so_Bk_Dt_DT)
							set @so_BK_Mt_I = DATEPART(m, @so_Bk_Dt_DT)
						end
						else
						begin
							set @so_Bk_Yr_I = DATEPART(yyyy, @ch_date)
							set @so_BK_Mt_I = DATEPART(m, @ch_date)
						end

						set @act_year = @so_Bk_Yr_I
						if (@so_BK_Mt_I >=1 and @so_BK_Mt_I <=3)
						begin
							set @so_Bk_Qt_I = 1
							set @so_Bk_Qt_End_I =3
						end
						else if (@so_BK_Mt_I >=4 and @so_BK_Mt_I <=6)
						begin
							set @so_Bk_Qt_I = 2
							set @so_Bk_Qt_End_I =6
						end
						else if (@so_BK_Mt_I >=7 and @so_BK_Mt_I <=9)
						begin
							set @so_Bk_Qt_I = 3
							set @so_Bk_Qt_End_I =9
						end						
						else if (@so_BK_Mt_I >=10 and @so_BK_Mt_I <=12)
						begin
							set @so_Bk_Qt_I = 4
							set @so_Bk_Qt_End_I =12
						end
						
						if(@bkd_Qty_I <> @temp_Qty_I)
						begin
							if ( @insert_Qty_I < 0 )
							set @neg_Pos_Bk_I = 1
							
							insert into @results values(@so_No_V, @so_Id_I, @cust_Id_I, @cool_Line_Item_Id_I, @cool_Line_Item_No_I,
									@prod_Id_I, @insert_Qty_I,@unit_Cost_D,(@insert_Qty_I * @unit_Cost_D), @so_Bk_Dt_DT, @ch_date, @so_Req_Dk_Dt_DT, @so_Schd_Dt_DT, @so_Rev_Dt_DT, 'HOLD',@neg_Pos_Bk_I,@so_Sales_Person_V,@so_End_User_V,@so_regional_Rep_V,@cust_Cust_Name_V,@prod_Name_V,@cust_Region_V,@so_Bk_Yr_I, @so_Bk_Mt_I, @so_Bk_Qt_I, @so_Bk_Qt_End_I,@act_year,@EndCustomer)
						
						end

						set @neg_Pos_Bk_I = 0

						if(@unit_Cost_D <> @temp_Unit_Cost_D)
						begin
							if ( @insert_Unit_Cost_D < 0 )
							set @neg_Pos_Bk_I = 1
							
							insert into @results values(@so_No_V, @so_Id_I, @cust_Id_I, @cool_Line_Item_Id_I, @cool_Line_Item_No_I,
									@prod_Id_I, @temp_Qty_I,@insert_Unit_Cost_D,(@temp_Qty_I * @insert_Unit_Cost_D), @so_Bk_Dt_DT, @ch_date , @so_Req_Dk_Dt_DT, @so_Schd_Dt_DT, @so_Rev_Dt_DT, 'HOLD',@neg_Pos_Bk_I,@so_Sales_Person_V,@so_End_User_V,@so_regional_Rep_V,@cust_Cust_Name_V,@prod_Name_V,@cust_Region_V,@so_Bk_Yr_I, @so_Bk_Mt_I, @so_Bk_Qt_I, @so_Bk_Qt_End_I,@act_year,@EndCustomer)
						end


						if (@bkd_Qty_I = @temp_Qty_I and @unit_Cost_D = @temp_Unit_Cost_D ) 
						begin
							set @neg_Pos_Bk_I = 0
						
							insert into @results values(@so_No_V, @so_Id_I, @cust_Id_I, @cool_Line_Item_Id_I, @cool_Line_Item_No_I,
									@prod_Id_I, 0 , 0.00 , 0.00 , @so_Bk_Dt_DT, @ch_date , @so_Req_Dk_Dt_DT, @so_Schd_Dt_DT, @so_Rev_Dt_DT, 'HOLD',@neg_Pos_Bk_I,@so_Sales_Person_V,@so_End_User_V,@so_regional_Rep_V,@cust_Cust_Name_V,@prod_Name_V,@cust_Region_V,@so_Bk_Yr_I, @so_Bk_Mt_I, @so_Bk_Qt_I, @so_Bk_Qt_End_I,@act_year,@EndCustomer)
						end


					set @neg_Pos_Bk_I = null
					set @so_Sales_Person_V = null
					set @so_regional_Rep_V = null
					set @so_Sales_Person_V = null
					set @cust_Region_V = null


					end
					
					set @cool_Line_Item_Id_I = null
					set @ch_date = null -- Added by Basha (11/20/2003)

					fetch next from LineItemCursor into @so_Id_I, @cool_Line_Item_Id_I, @cco_Ch_No_I, @cool_Line_Item_No_I, @prod_Id_I, @bkd_Qty_I,@unit_Cost_D,
						@amt_D, @so_Req_Dk_Dt_DT,@so_Schd_Dt_DT, @so_Rev_Dt_DT,@ch_date
				end
				close LineItemCursor
				deallocate LineItemCursor
	

	
			fetch next from ItemLevelCursor into @cursor_Li_Id_I 
			end
			close ItemLevelCursor
			deallocate ItemLevelCursor
	fetch next from mainCursor into @cursor_So_Id_I
	end
	close mainCursor
	deallocate mainCursor

	declare cancelLineItemCursor cursor for
	select CCOL.Salesorder_ID, LineItem_ID, CCOL_Line_Item_No, Product_ID,CCOL_Qty, CCOL_Unit_Cost,  
			CCOL_Amount, CCOL.Timestamp, CCOL_Requested_Date, CCOL_Schedule_Date, CCOL_Revised_Date , CCOL.Timestamp 
					 from  customer_cancel_order_lineitem CCOL,
					 Sales_Order SO where CCOL.SalesOrder_Id = SO.SalesOrder_Id and so.SO_Is_Sample = @order_type order by CCOL.SalesOrder_ID, LineItem_ID
						
	open cancelLineItemCursor
	fetch next from cancelLineItemCursor into @so_Id_I, @cool_Line_Item_Id_I, @cool_Line_Item_No_I, @prod_Id_I, @bkd_Qty_I,@unit_Cost_D,
						@amt_D, @so_Bk_Dt_DT, @so_Req_Dk_Dt_DT, @so_Schd_Dt_DT, @so_Rev_Dt_DT ,@ch_date
	while @@fetch_status = 0
	begin
		set @so_Bk_Dt_DT = null 
		select @so_Bk_Dt_DT = SO_Booking_Date from Sales_Order where SalesOrder_ID =  @so_Id_I	and Sales_Order.SO_Is_Sample = @order_type

		select @so_No_V= SO.SalesOrder_NO, @cust_Id_I = SO.Customer_ID, @so_sales_Person_V= SO.SO_Sales_Person, 
				@so_End_User_V = CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID <= 0  ) 
						THEN ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.Customer_ID )
						ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ODM_ID )
						END,
				@so_regional_Rep_V = Regional_Rep,
				@EndCustomer = CASE WHEN ( SO.SO_ENDCUSTOMER_ID IS NULL OR SO.SO_ENDCUSTOMER_ID <= 0  ) 
						THEN (CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID <= 0 ) 
							THEN ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.Customer_ID )
							ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID =  SO.SO_ODM_ID )
							END) 
						ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ENDCUSTOMER_ID)
						END 
			 from Sales_Order SO where SO.salesorder_ID = @so_ID_I and SO.SO_Is_Sample = @order_type
		select @cust_Cust_Name_V=C_Name, @cust_Region_V=C_Region from Customer where Customer_ID = @cust_Id_I
		select @prod_Name_V = part_Number from product where Product_ID = @prod_Id_I			set @bkd_Qty_I= -@bkd_Qty_I
			set @unit_Cost_D =-@unit_Cost_D
			set @amt_D = -@amt_D
		if ( @date_category is not null AND @date_category = 'BOOKING' )
		begin
			set @so_Bk_Yr_I = DATEPART(yyyy, @so_Bk_Dt_DT)
			set @so_BK_Mt_I = DATEPART(m, @so_Bk_Dt_DT)
		end
		else
		begin
			set @so_Bk_Yr_I = DATEPART(yyyy, @ch_date)
			set @so_BK_Mt_I = DATEPART(m, @ch_date)
		end
		set @act_year = @so_Bk_Yr_I -- Added By Basha (11/24/2003)

		if (@so_BK_Mt_I >=1 and @so_BK_Mt_I <=3)
		begin
			set @so_Bk_Qt_I = 1
			set @so_Bk_Qt_End_I =3
		end
		else if (@so_BK_Mt_I >=4 and @so_BK_Mt_I <=6)
		begin
			set @so_Bk_Qt_I = 2
			set @so_Bk_Qt_End_I =6
		end
		else if (@so_BK_Mt_I >=7 and @so_BK_Mt_I <=9)
		begin
			set @so_Bk_Qt_I = 3
			set @so_Bk_Qt_End_I =9
		end						
		else if (@so_BK_Mt_I >=10 and @so_BK_Mt_I <=12)
		begin
			set @so_Bk_Qt_I = 4
			set @so_Bk_Qt_End_I =12
		end
		
		insert into @results values(@so_No_V, @so_Id_I, @cust_Id_I, @cool_Line_Item_Id_I, @cool_Line_Item_No_I, @prod_Id_I, @bkd_Qty_I,@unit_Cost_D,
						@amt_D, @so_Bk_Dt_DT, @ch_date, @so_Req_Dk_Dt_DT, @so_Schd_Dt_DT, @so_Rev_Dt_DT ,'CANCELLED',1, @so_Sales_Person_V,@so_End_User_V,@so_regional_Rep_V,@cust_Cust_Name_V,@prod_Name_V, @cust_Region_V,@so_Bk_Yr_I, @so_Bk_Mt_I, @so_Bk_Qt_I, @so_Bk_Qt_End_I,@act_year,@EndCustomer)
		set @so_Sales_Person_V = null
		set @so_regional_Rep_V = null
		set @so_Sales_Person_V = null
		set @cust_Region_V = null


		set @ch_date = null -- Added By Basha (11/24/2003)

		fetch next from cancelLineItemCursor into @so_Id_I, @cool_Line_Item_Id_I, @cool_Line_Item_No_I, @prod_Id_I, @bkd_Qty_I,@unit_Cost_D,
						@amt_D, @so_Bk_Dt_DT, @so_Req_Dk_Dt_DT,@so_Schd_Dt_DT, @so_Rev_Dt_DT ,@ch_date
	end
	close cancelLineItemCursor
	deallocate cancelLineItemCursor

	if ( @date_category is not null AND @date_category = 'BOOKING' )
	begin
		select * from @results order by  BookDate , BookingMonth, NegativePositiveBk, LineItem_No
	end
	else
	begin
		select * from @results order by  ChangeDate , BookingMonth, NegativePositiveBk, LineItem_No
	end
